﻿using Spire.Xls;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;

namespace YWM.DoubleCommom
{
    public static class ExcelUtil
    {
        public static List<T> Import<T>(string path)
        {
            using (Workbook workbook = new Workbook())
            {
                workbook.LoadFromFile(path);
                using (Worksheet worksheet = workbook.Worksheets[0])
                {
                    DataTable dataTable = worksheet.ExportDataTable();
                    PropertyInfo[] propertyInfos = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
                    List<T> list = new List<T>();
                    foreach (DataRow row in dataTable.Rows)
                    {
                        T t = Activator.CreateInstance<T>();
                        foreach (PropertyInfo propertyInfo in propertyInfos)
                        {
                            string propertyName = propertyInfo.Name;
                            if (dataTable.Columns.IndexOf(propertyInfo.Name) == -1)
                            {
                                continue;
                            }
                            Type propType;
                            if (propertyInfo.PropertyType.IsGenericType
                                && propertyInfo.PropertyType.GetGenericTypeDefinition().FullName.Contains(typeof(Nullable).ToString()))
                            {
                                propType = Nullable.GetUnderlyingType(propertyInfo.PropertyType);
                            }
                            else
                            {
                                propType = propertyInfo.PropertyType;
                            }
                            object value = Convert.ChangeType(row[propertyName], propType);
                            propertyInfo.SetValue(t, value);
                        }
                        list.Add(t);
                    }
                    return list;
                }
            }
        }

        public static void Export(ICollection list, string path, Dictionary<string, string> pairs, string dateFormatString = "yyyy-MM-dd hh:mm:ss")
        {
            using (Workbook workbook = new Workbook())
            {
                using (Worksheet worksheet = workbook.Worksheets[0])
                {
                    int columIndex = 1;
                    foreach (string value in pairs.Values)
                    {
                        worksheet.Range[1, columIndex].Value = value;
                        worksheet.AutoFitColumn(columIndex);
                        columIndex++;
                    }
                    int rowIndex = 2;
                    string[] numerics = { typeof(sbyte).FullName, typeof(short).FullName, typeof(int).FullName, typeof(long).FullName, typeof(byte).FullName, typeof(ushort).FullName, typeof(uint).FullName, typeof(ulong).FullName, typeof(double).FullName, typeof(float).FullName, typeof(decimal).FullName };
                    foreach (object item in list)
                    {
                        Type type = item.GetType();
                        if (!type.IsClass)
                        {
                            return;
                        }
                        columIndex = 1;
                        foreach (string key in pairs.Keys)
                        {
                            PropertyInfo propertyInfo = type.GetProperty(key, BindingFlags.Public | BindingFlags.Instance);
                            object value = propertyInfo.GetValue(item, null);
                            if (value == null)
                            {
                                continue;
                            }
                            Type propertyType = propertyInfo.PropertyType;
                            if (propertyType.FullName.Equals(typeof(DateTime).FullName))
                            {
                                worksheet.Range[rowIndex, columIndex].DateTimeValue = Convert.ToDateTime(value);
                                if (!string.IsNullOrWhiteSpace(dateFormatString))
                                {
                                    worksheet.Range[rowIndex, columIndex].NumberFormat = dateFormatString;
                                }
                            }
                            else if (propertyType.FullName == typeof(string).FullName)
                            {
                                worksheet.Range[rowIndex, columIndex].Value = value.ToString();
                            }
                            else if (numerics.Contains(propertyType.FullName))
                            {
                                worksheet.Range[rowIndex, columIndex].NumberValue = Convert.ToDouble(value);
                            }
                            else if (propertyType.IsGenericType && propertyType.GetGenericTypeDefinition().FullName.Contains(typeof(Nullable).ToString()))
                            {
                                Type nullablePropertyType = Nullable.GetUnderlyingType(propertyType);
                                if (nullablePropertyType.FullName.Equals(typeof(DateTime).FullName))
                                {
                                    worksheet.Range[rowIndex, columIndex].DateTimeValue = Convert.ToDateTime(value);
                                    if (!string.IsNullOrWhiteSpace(dateFormatString))
                                    {
                                        worksheet.Range[rowIndex, columIndex].NumberFormat = dateFormatString;
                                    }
                                }
                                else if (numerics.Contains(nullablePropertyType.FullName))
                                {
                                    worksheet.Range[rowIndex, columIndex].NumberValue = Convert.ToDouble(value);
                                }
                            }
                            worksheet.AutoFitColumn(columIndex);
                            columIndex++;
                        }
                        rowIndex++;
                    }
                    //冻结第一行
                    worksheet.FreezePanes(1, 1);
                    workbook.SaveToFile(path);
                }
            }
        }
    }
}
